package com.thb.business.dao.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.thb.business.dao.IArticleDao;
import com.thb.business.model.Article;
import com.thb.business.model.ArticlePara;

@Repository
public class IArticleDaoImpl implements IArticleDao {

	@Autowired
	private JdbcTemplate jTemplate;

	@Override
	public List<Article> findByPara(ArticlePara para) {
		StringBuilder sql = new StringBuilder("");
		sql.append("select a.* from t_article a  ");
		
		if(para.getColumnId() != 0){
			sql.append("join t_article_column b on a.id = b.article_id ");
		}
		
		sql.append("where ");
		
		List<Object> args = new ArrayList<Object>();
		List<Integer> argsType = new ArrayList<Integer>();
		
		if(para.getColumnId() != 0){
			sql.append("and b.column_id = ? ");
			args.add(para.getColumnId());
			argsType.add(Types.INTEGER);
		}
		if(para.getHotType() != 0){
			sql.append("and a.hot_type = ? ");
			args.add(para.getHotType());
			argsType.add(Types.INTEGER);
		}
		
		if(null != para.getSource() && !"".equals(para.getSource())){
			sql.append("and a.source = ? ");
			args.add(para.getSource());
			argsType.add(Types.VARCHAR);
		}
		
		if(null != para.getTitle() && !"".equals(para.getTitle())){
			sql.append("and a.title like ? ");
			args.add("%" + para.getTitle() +"%");
			argsType.add(Types.VARCHAR);
		}
		
		if(para.getPage() != 0){
			sql.append("limit ?,?");
			args.add((para.getPage()-1)*para.getPageSize());
			argsType.add(Types.INTEGER);
			args.add(para.getPageSize());
			argsType.add(Types.INTEGER);
		}
		
		String asql = sql.toString();
		asql = asql.replace("where and", "where ");
		asql = asql.replace("where limit", " limit ");
		
		Object[] arg = args.toArray();
		Integer[] argTypes = argsType.toArray(new Integer[]{});
		
		int[] typesi = new int[argTypes.length];
		for(int i =0;i<argTypes.length;i++){
			typesi[i] = argTypes[i].intValue();
		}
		
		
		return jTemplate.query(asql, arg,typesi,new RowMapper<Article>() {
			
			@Override
			public Article mapRow(ResultSet rs, int no) throws SQLException {

				return jsonMapper(rs, no);
			}
		});
	}
	
	@Override
	public List<Article> findSpecialByPara(ArticlePara para) {
		StringBuilder sql = new StringBuilder("");
		sql.append("select a.* from t_article a  ");
		
		sql.append("where ");
		
		List<Object> args = new ArrayList<Object>();
		List<Integer> argsType = new ArrayList<Integer>();
		
		if(para.getParId() != 0){
			sql.append("and a.par_id = ? ");
			args.add(para.getParId());
			argsType.add(Types.INTEGER);
		}
		
		if(para.getLabel() != null && !"".equals(para.getLabel())){
			sql.append("and a.label = ? ");
			args.add(para.getLabel());
			argsType.add(Types.VARCHAR);
		}
		
		
		String asql = sql.toString();
		asql = asql.replace("where and", "where ");
		
		Object[] arg = args.toArray();
		Integer[] argTypes = argsType.toArray(new Integer[]{});
		
		int[] typesi = new int[argTypes.length];
		for(int i =0;i<argTypes.length;i++){
			typesi[i] = argTypes[i].intValue();
		}
		
		
		return jTemplate.query(asql, arg,typesi,new RowMapper<Article>() {
			
			@Override
			public Article mapRow(ResultSet rs, int no) throws SQLException {

				return jsonMapper(rs, no);
			}
		});
	}

	private Article jsonMapper(ResultSet rs, int no) throws SQLException {

		Article vo = new Article();
		vo.setId(rs.getInt("id"));
		vo.setTitle(rs.getString("title"));
		vo.setContent(rs.getString("content"));
		vo.setCreateTime(rs.getString("create_time"));
		vo.setHotType(rs.getInt("hot_type"));
		vo.setImg1(rs.getString("img1"));
		vo.setImg2(rs.getString("img2"));
		vo.setImg3(rs.getString("img3"));
		vo.setType(rs.getInt("type"));
		vo.setSource(rs.getString("source"));
		vo.setLabel(rs.getString("label"));
		vo.setParId(rs.getInt("par_id"));
		
		return vo;
	}

	@Override
	public int countByPara(ArticlePara para) {
		StringBuilder sql = new StringBuilder("");
		sql.append("select count(*) from t_article a  ");
		
		if(para.getColumnId() != 0){
			sql.append("join t_article_column b on a.id = b.article_id ");
		}
		
		sql.append("where ");
		
		List<Object> args = new ArrayList<Object>();
		List<Integer> argsType = new ArrayList<Integer>();
		
		if(para.getColumnId() != 0){
			sql.append("and b.column_id = ? ");
			args.add(para.getColumnId());
			argsType.add(Types.INTEGER);
		}
		if(para.getHotType() != 0){
			sql.append("and a.hot_type = ? ");
			args.add(para.getHotType());
			argsType.add(Types.INTEGER);
		}
		if(null != para.getSource() && !"".equals(para.getSource())){
			sql.append("and a.source = ? ");
			args.add(para.getSource());
			argsType.add(Types.VARCHAR);
		}
		
		
		String asql = sql.toString();
		if(asql.indexOf("where and") >0){
			asql = asql.replace("where and", "where ");
		}else{
			asql = asql.replace("where ", " ");
		}
		
		
		Object[] arg = args.toArray();
		Integer[] argTypes = argsType.toArray(new Integer[]{});
		
		int[] typesi = new int[argTypes.length];
		for(int i =0;i<argTypes.length;i++){
			typesi[i] = argTypes[i].intValue();
		}
		
		
		return jTemplate.queryForInt(asql, arg,typesi);
	}

	

}
